using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using MySql.Data.MySqlClient;
using System.Data;

namespace PFDB.AppLib.MYSQLSET
{
    class DBTablesManage
    {
        MySqlConnection conn; 
        public DBTablesManage(string DBName,string username, string password,string server)
        {
            conn= DBConnect.conn(DBName,username,password,server);
                conn.Open();
        }
        public static ArrayList GetTables(string DBName ,string username, string password,string server)
        {
            ArrayList tables=new ArrayList();
            MySqlConnection conn=DBConnect.conn(DBName, username, password, server);
            conn.Open();
            MySqlCommand comm = new MySqlCommand("select table_name from information_schema.tables where table_schema='"+DBName+"';",conn);
            MySqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                tables.Add((string)dr[0]);
            }
            conn.Close();
            return tables;
        }
        public static ArrayList GetDatabases(string username, string password, string server)
        {
            ArrayList databases = new ArrayList();
            MySqlConnection conn = DBConnect.conn("mysql",username,password,server);
            conn.Open();
            MySqlCommand comm = new MySqlCommand("show databases", conn);
            MySqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                databases.Add((string)dr[0]);
            }
            conn.Close();
            return databases;
        }
        public static ArrayList GetSP(string DBName, string username, string password, string server)
        {
            ArrayList SPs = new ArrayList();
            MySqlConnection conn = DBConnect.conn("mysql",username,password,server);
            conn.Open();
            MySqlCommand comm = new MySqlCommand("select name from proc where db=\'"+DBName+"\'", conn);
            MySqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                SPs.Add((string)dr[0]);
            }
            conn.Close();
            return SPs;
        }
        public string getSPBody(string DBName, string SPname, string username, string password, string server)
        {

            MySqlConnection conn = DBConnect.conn("mysql",username,password,server);
            conn.Open();
            MySqlCommand comm = new MySqlCommand("select convert(body using utf8) from proc where name=\'"+SPname+ "\' AND db=\'"+DBName+"\'", conn);
            MySqlDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
            if (dr.Read())
            {
                byte [] body= (byte[])dr[0];
                string str;
                System.Text.UTF8Encoding enc = new System.Text.UTF8Encoding();
                str = enc.GetString(body);
                conn.Close();
                return str;
            }

            return String.Empty;
        }

        public string getSPParamList(string DBName, string SPname, string username, string password, string server)
        {
            MySqlConnection conn = DBConnect.conn("mysql",username,password,server);
            conn.Open();
            MySqlCommand comm = new MySqlCommand("select convert(param_list using utf8) from proc where name=\'" + SPname + "\' AND db=\'" + DBName+"\'", conn);
            MySqlDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
            if (dr.Read())
            {
                byte[] dBytes = (byte[])dr[0];
                string str;
                System.Text.UTF8Encoding enc = new System.Text.UTF8Encoding();
                str = enc.GetString(dBytes);
                conn.Close();
                return str;
            }

            return String.Empty;
        }
        public MySqlDataReader getInfo(string DBName, string tablename)
        {

            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            DataTable table = new DataTable();
            MySqlCommand comm = new MySqlCommand("describe "+tablename, conn);
            MySqlDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
            return dr;
        }
    }
}
